<?php
include_once './application/application.php';
include_once APP_LIB_FILES.'sxgeo/sxgeo.php';
include_once APP_LIB_FILES.'browser_detection.php';

class Page_charts {

    public $action;
    public $error;

    public function __construct() {
        $this->action = Arr::getValue( $_POST, 'action', false );
        DB::connect();
        if ( !DB::state() ) {
            $this->error = DB::getError();
        }
    }

    private function date_convert( $thedate, $withTime=null ) {
        $day  = substr( $thedate, 0, 10 );
        $time = substr( $thedate, -8 );

        if ( $day == date( "Y-m-d" ) )
            $newdate = "Сегодня";
        elseif ( $day == date( "Y-m-d", intval( time() - 24 * 60 * 60 ) ) )
            $newdate = "Вчера";
        else
            $newdate = substr( $thedate, 8, 2 )."-".substr( $thedate, 5, 2 )."-".substr( $thedate, 0, 4 );

        if ( $withTime ) {
            $newdate = sprintf(
                '<span style="white-space: nowrap;">%s</span> <span style="white-space: nowrap;">%s</span>',
                $newdate, $time
            );
        }
        return $newdate;
    }

        
        
    /**
     * json_encode wrapper with json pretty print (for php < 5.4) and <pre><code> result wrapper
     *
     * @param any     $json      [description]
     * @param boolean $object    if TRUE: json_encode($json)
     * @param boolean $_pre_code if TRUE: wrap result <pre><code>
     * @return string             json string
     */
    private function json_p( &$json, $object = FALSE, $_pre_code = FALSE ) {
        if ( $object )
            $json = json_encode( $json );
        $result = '';
        $level = 0;
        $prev_char = '';
        $in_quotes = FALSE;
        $ends_line_level = NULL;
        $json_length = strlen( $json );

        for ( $i = 0; $i < $json_length; $i++ ) {
            $char = $json[$i];
            $new_line_level = NULL;
            $post = "";
            if ( $ends_line_level !== NULL ) {
                $new_line_level = $ends_line_level;
                $ends_line_level = NULL;
            }
            if ( $char === '"' && $prev_char != '\\' ) {
                $in_quotes = !$in_quotes;
            } else if ( ! $in_quotes ) {
                    switch ( $char ) {
                    case '}': case ']':
                        $level--;
                        $ends_line_level = NULL;
                        $new_line_level = $level;
                        break;

                    case '{': case '[':
                        $level++;
                    case ',':
                        $ends_line_level = $level;
                        break;

                    case ':':
                        $post = " ";
                        break;

                    case " ": case "\t": case "\n": case "\r":
                        $char = "";
                        $ends_line_level = $new_line_level;
                        $new_line_level = NULL;
                        break;
                    }
                }
            if ( $new_line_level !== NULL ) {
                $result .= "\n".str_repeat( "\t", $new_line_level );
            }
            $result .= $char.$post;
            $prev_char = $char;
        }
        $json = $result;
        if ( $_pre_code )
            $json = '<pre><code class="json">'.$json.'</code></pre>';
        return $json;
    }

    private function ip2int( $ip ) {
        $part = explode( ".", $ip );
        $int  = 0;
        if ( count( $part ) == 4 )
            $int = $part[3] + 256 * ( $part[2] + 256 * ( $part[1] + 256 * $part[0] ) );
        return $int;
    }

    private function find_geoip( $ip, &$sxgeo ) {
        $geo = $sxgeo->getCity( $ip );
        if ( $geo['city'] ) return $geo['city'];
        else return 'неизвестно';
    }

    private function renderTable( &$arr, $thead ) {
        $table = '<table class="table table-striped table-bordered table-condensed">';
        if ( $thead ) {
            $table .= '<thead><tr>';
            foreach ( $thead as $h )
                $table .= '<th>'.$h.'</th>';
            $table .= '</tr></thead>';
        }
        foreach ( $arr as $item ) {
            $table .= '<tbody><tr>';
            foreach ( $item as $k => $v ) {
                switch ( $k ) {
                case 'photo':
                    if ( $v != NULL )
                        $v = '<div class="userphoto-container min"><img src="'.$v.'" /></div>';
                    else
                        $v = '<div class="userphoto-container min"><img src="/app/img/0.png" /></div>';
                    break;
                }
                $table .= sprintf(
                    '<td title="%s" class="tbl-%s">%s</td>',
                    htmlspecialchars( $thead[$k] ),
                    $k, $v
                );
            }
            $table .= '</tr></tbody>';
        }
        $table .= '</table>';
        return $table;
    }

        
    public function __destruct() {}

    /**
     * [Посещения 15 дней](http://localhost/admin6/charts.php?lm-last-15)
     */
    public function getLast15() {
            $params = array();
            $users = array();
            $users_ss = '';

            $result = DB::query(
                    "SELECT COUNT(users_log_action.id)      as co,
                            DAY(users_log_action.log_date)  as da,
                            DATE(users_log_action.log_date) as date,
                            YEAR(users_log_action.log_date) as ya  
                        FROM users_log_action, users_list

                        WHERE users_list.id = users_log_action.user_id
                          AND users_list.deleted = '0'
                          AND users_log_action.log_type =  '1'
                          AND users_log_action.user_id not in (6,13,90)
                          AND DATE_SUB( CURDATE( ) , INTERVAL 15 DAY ) <= users_log_action.log_date

                        GROUP BY date(users_log_action.log_date) "
                );

            while ($cols = mysqli_fetch_array($result, MYSQLI_ASSOC)) {
                $users_count1 = $cols["co"];
                $users_y1 = $cols["ya"];
                $users_ss .= "['" . $cols["da"] . "'," . $cols["co"] . "],";
                array_push(
                        $users, 
                        (object) array(
                                'date'  => $cols['date'],
                                'value' => $cols["co"]
                            )
                );
            }

            $jUsers = json_encode($users);
            $content = "<div data-charts='$jUsers'></div>";
            $this->printOut( includeFile( APP_PAGE_PARTS.'page_charts.php', $params ) . $content );
    }

        
        
    /**
     * Топ 10 пользователей
     * charts.php?lm-top-10-users
     */
    public function getTop10Users() {
        $params = array();
        $top10_user_ss = "";
        $cua = array();
        $result = DB::query(
            "SELECT users_list.first_name, users_list.last_name, COUNT(users_list.login) AS con_count
                FROM users_log_action, users_list
                WHERE
                  users_list.id = users_log_action.user_id AND
                  users_list.deleted = '0' AND
                  users_log_action.log_type = '1' AND
                  users_log_action.user_id not in (6,13,90) AND
                  DATE_SUB( CURDATE( ) , INTERVAL 30 DAY ) <= users_log_action.log_date
                GROUP BY users_list.login
                ORDER BY con_count DESC
                LIMIT 30" );
        $top10_user = array();
        while ( $cols = $result->fetch_array( MYSQLI_ASSOC ) ) {
            $first_name = "";
            if ( strlen( $cols["first_name"] ) > 1 )
                $first_name = mb_substr( $cols["first_name"], 0, 1, 'UTF-8' ).". ";

            array_push( $top10_user, (object) array(
                    'title'  => (string) $first_name.$cols["last_name"],
                    'value' =>  (int)    $cols["con_count"]
                ) );
        }
        $jUsers = json_encode( $top10_user  );
        $content = "<div data-charts='$jUsers'></div>";
        $this->printOut( includeFile( APP_PAGE_PARTS.'page_charts.php', $params ) . $content );
    }

    /**
     * Браузеры и устройства
     * charts.php?lm-browsers
     */
    public function getBrowsers( $devices = FALSE ) {
        $params = array();
        $dev_array = array();
        $bro_array = array();

        $result = DB::query(
            "SELECT users_log_action.user_agent as ua, count(users_log_action.user_agent) as cua
                FROM users_log_action, users_list
                WHERE users_list.id = users_log_action.user_id
                  AND users_list.deleted = '0'
                  AND users_log_action.log_type =  '1'
                  AND users_log_action.user_id NOT IN (6,13,90)
                  AND DATE_SUB( CURDATE( ), INTERVAL 30 DAY ) <= users_log_action.log_date
                GROUP BY users_log_action.user_agent" );

        while ( $cols = mysqli_fetch_array( $result, MYSQLI_ASSOC ) ) {
            $dev = $cols["ua"];
            switch ( true ) {
            case stristr( $cols["ua"], "iPhone" ):
                $dev = "iPhone";
                break;
            case stristr( $cols["ua"], "iPad" ):
                $dev = "iPad";
                break;
            case stristr( $cols["ua"], "Macintosh" ):
                $dev = "Mac OS X";
                break;
            default:
                $dev = "PC";
                break;
            }

            switch ( true ) {

            case stristr( $cols["ua"], "iPhone" ):
                $bro = "iPhone";
                break;

            case stristr( $cols["ua"], "iPad" ):
                $bro = "iPad";
                break;

            default:
                $bro = ucwords( browser_detection( 'browser_name', '', $cols["ua"] ) );
                if ( stristr( $bro, "MSIE" ) )
                    $bro = "IE ".browser_detection( 'browser_number', '', $cols["ua"] );
                break;
            }

            if ( !array_key_exists( $dev, $dev_array ) ) $prev = 0;
            else $prev = $dev_array[$dev];

            $dev_array[$dev] = $prev + $cols["cua"];

            if ( !array_key_exists( $bro, $bro_array ) )
                $prev = 0;
            else $prev = $bro_array[$bro];
            $bro_array[$bro] = $prev + $cols["cua"];
        }
        arsort( $bro_array );

        $s = (object) array(
            'browsers' => array(),
            'devices'  => array()
        );
        foreach ( $bro_array as $t => $v ) {
            array_push(
                $s->browsers,
                (object) array( 'title' => $t, 'value' => $v )
            );
        }

        foreach ( $dev_array as $t => $v ) {
            array_push(
                $s->devices,
                (object) array( 'title' => $t, 'value' => $v )
            ); 
        }

        /*header('content-type: text/plain; charset: utf-8');
      print json_encode($s);*/
        $jBrowsers = json_encode( $s->browsers );
        $jDevices  = json_encode( $s->devices );
        $this->printOut(
         includeFile( APP_PAGE_PARTS.'page_charts.php', $params )
         ."<h3>Браузеры</h3>\n
         <div data-charts='$jBrowsers'></div>\n
         <h3>Устройства</h3>\n
         <div data-charts='$jDevices'></div>\n"
        );
    }

    /**
     * Посещения 300
     * charts.php?lm-last-300
     */
    public function getLast300() {
        $params = array();
        $J = new JSON();
        $result = DB::query(
            "SELECT
                  users_list.photo,
                  users_list.first_name,
                  users_list.last_name,
                  users_list.organisation,
                  users_list.job_position,
                  users_log_action.log_date,
                  users_log_action.user_agent,
                  users_action_types.title
                FROM users_list, users_log_action, users_action_types
                WHERE
                  users_list.id = users_log_action.user_id AND
                  users_list.deleted = '0' AND
                  users_log_action.user_id not in (6,13,90) AND
                  users_log_action.log_type=users_action_types.type
                GROUP BY users_log_action.log_date, users_list.login
                ORDER BY users_log_action.log_date DESC
                LIMIT 300" );

        if ( !$result || !DB::state() ) return $J->error( 'db_select_err' );

        $i = 0;

        $last_300       = array();
        $thead = array(
            'photo'         => 'Фото',
            'user'          => 'Пользователь',
            'date'          => 'Дата',
            'organisation'  => 'Организация',
            'position'      => 'Должность',
            'device'        => 'Устройство',
            'browser'       => 'Браузер',
            'log_type'      => 'Лог'
        );
        while ( $cols = $result->fetch_array( MYSQLI_ASSOC ) ) {
            $first_name = "";
            if ( strlen( $cols["first_name"] )>1 ) {
                $first_name = mb_substr( $cols["first_name"], 0, 1, 'UTF-8' ).". ";
            }
            $device = "PC";
            switch ( true ) {
            case strstr( $cols["user_agent"], "iPhone" ):
                $device = "iPhone";
                break;
            case strstr( $cols["user_agent"], "iPad" ):
                $device = "iPad";
                break;
            case strstr( $cols["user_agent"], "Macintosh" ):
                $device = "Mac OS X";
                break;
            }
            $browser = $cols["user_agent"];
            $bd = browser_detection( 'full_assoc', '', $browser );
            switch ( $bd["browser_name"] ) {
                case 'NA':
                    break;
                case 'msie':
                    $browser = "IE ".$bd["browser_number"];
                    break;
                case 'gecko':
                    $browser = "Firefox ".$bd["moz_data"][1];
                    break;
                case 'chrome':
                    $browser = ucfirst( $bd["browser_name"] )." ".$bd["webkit_data"][1];
                    break;
                case 'applewebkit':
                    $browser = "Safari ".$bd["browser_number"];
                    break;
                default:
                    $browser = ucfirst( $bd["browser_name"] )." ".$bd["browser_number"];
                    break;
            }
            array_push( $last_300, array(
                    'photo'        => $cols["photo"],
                    'user'         => $first_name.$cols["last_name"],
                    'date'         => $this->date_convert( $cols["log_date"], true ),
                    'organisation' => $cols["organisation"],
                    'position'     => $cols["job_position"],
                    'device'       => $device,
                    'browser'      => $browser,
                    'log_type'     => $cols["title"]
                ) );
            $i++;
        }

        $table = $this->renderTable( $last_300, $thead );
        $this->printOut( includeFile( APP_PAGE_PARTS.'page_charts.php', $params ) . $table );
    }

    /**
     * Посещения по дням
     * charts.php?lm-users-daily
     */
    public function getUsersDaily() {
        $params = array();
        $result = DB::query(
            "SELECT
                users_list.photo,
                users_list.first_name,
                users_list.last_name,
                users_list.organisation,
                users_list.job_position,
                date(users_log_action.log_date) as thedate,
                COUNT(users_list.login) AS count
            FROM users_list, users_log_action
            WHERE
                users_list.id = users_log_action.user_id AND
                users_list.deleted = '0' AND
                users_log_action.log_type =  '1' AND
                users_log_action.user_id not in (6,13,90)
            GROUP BY users_list.login,thedate
            ORDER BY thedate DESC
            LIMIT 300" );

        $users_daily = array();
        $thead = array(
            'photo'         => 'Фото',
            'user'          => 'Пользователь',
            'number'        => 'Кол-во',
            'organisation'  => 'Организация',
            'position'      => 'Должность'
        );
        while ( $cols = $result->fetch_array( MYSQLI_ASSOC ) ) {
            $first_name = "";
            if ( strlen( $cols["first_name"] ) > 1 )
                $first_name = mb_substr( $cols["first_name"], 0, 1, 'UTF-8' ).". ";
            // $date = $this->date_convert($cols['thedate'], false);
            $date = $cols['thedate'];
            $users_daily[$date][] = array(
                'photo'        => $cols["photo"],
                'user'         => $first_name.$cols['last_name'],
                'number'       => $cols['count'],
                'organisation' => $cols['organisation'],
                'position'     => $cols['job_position']
            );
        }
        // print_r($users_daily);
        // die();
        $table = '';
        foreach ( $users_daily as $date => $t )
            $table .= sprintf(
                '<div class="well well-small span7" style="color: #666;">
                <div>
                  <h3>%s</h3>
                  <button id="exp-date-%s" class="btn btn-info" onclick="toggle_ds(this);return false;" title="Свернуть/развернуть">
                    <i class="icon-chevron-down icon-white"></i>
                    <span> Развернуть</span>
                  </button>
                </div>
                <div id="exp-date-%s-content" class="hidden">%s</div>
            </div>',
                $date,
                $date,
                $date,
                $this->renderTable( $t, $thead )
            );
        $this->printOut( includeFile( APP_PAGE_PARTS.'page_charts.php', $params ) . $table );
    }

    /**
     * Запросы
     * charts.php?lm-queries
     */
    public function getQueries() {
        $params = array();
        $rUser  = (int) Arr::getValue( $_REQUEST, 'user', 0 );

        if ( $rUser )
            $where = "WHERE user_id = '$rUser'";
        else
            $where = '';

        $q = "SELECT count(id) AS cnt, page, description, date_time AS last_requested
              FROM users_log_pages $where
              GROUP BY page ORDER BY cnt DESC, last_requested DESC";

        $visitedPages = array();

        $result = DB::query( $q );
        while (  $row = $result->fetch_array( MYSQLI_ASSOC )  ) {
            array_push( $visitedPages, array(
                    'title' => $row['description'],
                    'value' => $row['cnt']
                ) );
        }
        $jPages = json_encode( $visitedPages );
        $this->printOut("<div data-charts='$jPages'></div>");
    }

    /**
     * Пользователи онлайн
     * charts.php?lm-users-online
     */   
    public function usersOnline() {
        $params = array();
        $users_online_ss = array();
        $result = DB::query(
            "SELECT
            users_online.user_id,
            users_list.photo,
            users_list.first_name,
            users_list.last_name,
            users_list.organisation,
            users_list.job_position,
            users_online.logged_in,
            users_log_action.user_agent,
            users_online.ip
            FROM users_online, users_list, users_log_action
            WHERE
                users_list.id = users_online.user_id AND
                users_log_action.user_id = users_online.user_id
            GROUP BY users_log_action.user_id
            ORDER BY users_log_action.log_date DESC, users_online.logged_in");
        $i = 0;
        $users_online = array();
        $thead = array(
            'photo'         => 'Фото',
            'user'          => 'Пользователь',
            'connetcted'    => 'Подключен',
            'organisation'  => 'Организация',
            'position'      => 'Должность',
            'device'        => 'Устройство',
            'location'      => 'Находится'
        );
        $sxgeo = new SxGeo( APP_LIB_FILES.'sxgeo/sxgeocity.dat', SXGEO_BATCH | SXGEO_MEMORY );
        while ( $cols = $result->fetch_array( MYSQLI_ASSOC ) ) {
            $first_name = "";
            if ( strlen( $cols["first_name"] )>1 )
                $first_name = mb_substr( $cols["first_name"], 0, 1, 'UTF-8' ).". ";

            $users_online_ss[$i][0] = $cols["photo"];
            $users_online_ss[$i][1] = $first_name.$cols["last_name"];
            $difference = time() - $cols["logged_in"];
            $hours   = floor( $difference / 3600 );
            $minutes = floor( ( $difference % 3600 ) / 60 );
            $users_online_ss[$i][2] = $hours. " ч. " .$minutes." м.";
            $users_online_ss[$i][3] = $cols["organisation"];
            $users_online_ss[$i][4] = $cols["job_position"];
            $device  = "";
            $browser = "";

            $device = "PC";
            switch ( true ) {
            case strstr( $cols["user_agent"], "iPhone" ):
                $device = "iPhone";
                break;
            case strstr( $cols["user_agent"], "iPad" ):
                $device = "iPad";
                break;
            case strstr( $cols["user_agent"], "Macintosh" ):
                $device = "Mac OS X";
                break;
            }
            $browser = $cols["user_agent"];
            $bd = browser_detection( 'full_assoc', '', $browser );
            switch ( $bd["browser_name"] ) {
            case 'NA':
                break;
            case 'msie':
                $browser = "IE ".$bd["browser_number"];
                break;
            case 'gecko':
                $browser = "Firefox ".$bd["moz_data"][1];
                break;
            case 'chrome':
                $browser = ucfirst( $bd["browser_name"] )." ".$bd["webkit_data"][1];
                break;
            case 'applewebkit':
                $browser = "Safari ".$bd["browser_number"];
                break;
            default:
                $browser = ucfirst( $bd["browser_name"] )." ".$bd["browser_number"];
                break;
            }

            $users_online_ss[$i][5] = "$device<br>$browser";
            // $users_online_ss[$i][6] = $this->find_geoip($this->ip2int($cols["ip"]), $result);
            $users_online_ss[$i][6] = $this->find_geoip( $cols["ip"], $sxgeo );
            array_push( $users_online, array(
                    'photo'        => $users_online_ss[$i][0],
                    'user'         => $users_online_ss[$i][1],
                    'connetcted'   => $users_online_ss[$i][2],
                    'organisation' => $users_online_ss[$i][3],
                    'position'     => $users_online_ss[$i][4],
                    'device'       => $users_online_ss[$i][5],
                    'location'     => $users_online_ss[$i][6]
                ) );
            $i++;
        }
        $table = $this->renderTable( $users_online, $thead );
        $this->printOut( includeFile( APP_PAGE_PARTS.'page_charts.php', $params ) . $table );
    }

    public function outPage() {
        $params = array();
        $this->printOut( '' );
        //$this->printOut(includeFile(APP_PAGE_PARTS.'page_contacts.php', $params));
    }

        
        
    public function printOut( $content = '' ) {
        include_once APP_PAGE_PARTS.'header.php';
        echo Navbar::getHorisontalBar();
        echo Auth::loginForm( true );
        $params = array(
            'leftmenu'  => Navbar::getVerticalBar( 'leftmenu' ), //includeFile(APP_PAGE_PARTS.'leftmenu.php'),
            'content'   => $content ? $content : ''
        );
        include APP_PAGE_PARTS.'columns.php';

        include_once APP_PAGE_PARTS.'footer.php';
        die();
    }
}
$page = new Page_charts();
Navbar::$expanded = 'lm-statistics';
Navbar::$active   = 'lm-charts';
if ( $page->action ) {
    //if ($page->action == ACTIONS::GET_LAST_300)   echo $page->getLast300();
} else {
    if ( !Auth::isLogged() ) reLocate ( 'index.php' );
    $qs = &$_SERVER['QUERY_STRING'];
    switch ( $qs ) {

    case 'lm-last-15':
        Navbar::$active = $qs;
        $page->getLast15();
        break;

    case 'lm-last-300':
        Navbar::$active = $qs;
        $page->getLast300();
        break;

    case 'lm-top-10-users':
        Navbar::$active = $qs;
        $page->getTop10Users();
        break;

    case 'lm-users-online':
        Navbar::$active = $qs;
        $page->usersOnline();
        break;

    case 'lm-browsers':
        Navbar::$active = $qs;
        $page->getBrowsers();
        break;

    case 'lm-users-daily':
        Navbar::$active = $qs;
        $page->getUsersDaily();
        break;

    case 'lm-queries':
        Navbar::$active = $qs;
        $page->getQueries();
        break;

    default:
        $page->printOut( 'default' );
        break;
    }

    /*if (array_key_exists('get_last_300', $_REQUEST)) {
        Navbar::$active = 'lm-last-300';
        $page->getLast300();
    }
    if (array_key_exists('get_last_300', $_REQUEST)) {
        Navbar::$active = 'lm-last-300';
        $page->getLast300();
    }*/

}

?>
